package com.eascs.workflow.aging.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.workflow.aging.dao.AgingDao;
import com.eascs.workflow.aging.entity.NodeEntity;
import com.eascs.workflow.aging.entity.ProcessInfoEntity;
import com.eascs.workflow.aging.entity.WorkFlowEntity;
import com.eascs.workflow.aging.form.AgingQueryForm;
import com.eascs.workflow.aging.form.NodeAgingForm;
import com.eascs.workflow.aging.form.ProcessAgingForm;
import com.eascs.workflow.aging.service.IAgingService;

@Service
public class AgingServiceImpl implements IAgingService {
	
	@Autowired
	AgingDao agingDao;

	@Override
	public PageResult<Object[]> getAgingInfoByPage(AgingQueryForm entity) {
		// TODO Auto-generated method stub
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getAgingIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT wf.refer_code , wf.org_name , wf.appliedby_name,wf.used_time as time1 ,wf.used_time as time2,wf.used_time as time3,wf.used_time as time4 , wf.overtime  FROM wf_process_task_main wf WHERE 1=1 ");
		
		if(!StringUtil.isEmpty(entity.getProcedureType())){
			querySql.append(" AND wf.process_type = ? "); 
			sqlPageQuery.addParam(entity.getProcedureType());
		}
		if(!StringUtil.isEmpty(entity.getBillCode())){
			querySql.append(" AND wf.refer_code = ? "); 
			sqlPageQuery.addParam(entity.getBillCode());
		}
		if(!StringUtil.isEmpty(entity.getCreateDateStart())){
			querySql.append(" AND wf.applied_date > ? ");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if(!StringUtil.isEmpty(entity.getCreateDateEnd())){
			querySql.append(" AND wf.applied_date < ? ");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}
		if(!StringUtil.isEmpty(entity.getLtdName())){
			querySql.append(" AND wf.org_name like ? ");
			sqlPageQuery.addLikeParam(entity.getLtdName());
		}
		if (entity.getOverIndex()) {
			querySql.append(" AND wf.overtime > 0  ");
		}
		querySql.append(" AND wf.process_status != '3' group by wf.refer_code  ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return agingDao.findDataWithPage(sqlPageQuery);
		
	}

	@Override
	public List<ProcessInfoEntity> getProcess() {
		// TODO Auto-generated method stub
		return agingDao.findEntitys("SELECT wd.code , wd.name  FROM wf_dictionary_data wd WHERE wd.is_lock = 'N' AND wd.type = ? ", ProcessInfoEntity.class, new Object[]{10});
	}

	@Override
	public PageResult<Object[]> getAgingDetail(String billCode, String processType) {
		// TODO Auto-generated method stub
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
			StringBuffer querySql = new StringBuffer();
			querySql.append(" SELECT node_name , auditor_start_date , auditor_end_date , ");
			querySql.append(" used_time , time_effective , ");
			querySql.append(" case when(used_time - time_effective) < 0 then 0 else (used_time - time_effective) end ");
			querySql.append(" from wf_process_task_node WHERE task_id = ");
			querySql.append("( SELECT task_id FROM wf_process_task_main WHERE refer_code = ? AND process_type = ? AND process_status != '3' )  ");
			sqlPageQuery.addParam(StringUtil.trimBlank(billCode));
			sqlPageQuery.addParam(StringUtil.trimBlank(processType));
			querySql.append(" and node_status != '5' ORDER BY node_seq ");
			sqlPageQuery.setQuerySql(querySql.toString());
			return agingDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public PageResult<WorkFlowEntity> getWorkFlow() {
		SqlPageQuery<WorkFlowEntity> sqlPageQuery = new SqlPageQuery<>(WorkFlowEntity.class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT  m.refer_code as billCode , m.refer_id as referId, M.TASK_ID as taskId,M.PROCESS_NAME as processName,M.PROCESS_TYPE as processType,M.TIME_EFFECTIVE as timeEffective,"
				+ " M.USED_TIME usedTime,M.OVERTIME as overtime FROM WF_PROCESS_TASK_MAIN M WHERE M.PROCESS_STATUS != '3'";
		sqlPageQuery.setQuerySql(querySql);
		return agingDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public PageResult<NodeEntity> getNode() {
		SqlPageQuery<NodeEntity> sqlPageQuery = new SqlPageQuery<>(NodeEntity.class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT N.TASK_ID as taskId, N.NODE_NAME as nodeName , N.AUDITOR_START_DATE as auditorStartDate , N.AUDITOR_END_DATE as auditorEndDate , "
				+ " N.TIME_EFFECTIVE as timeEffective , N.USED_TIME as usedTime FROM WF_PROCESS_TASK_NODE N WHERE N.NODE_STATUS != '5' ";
		sqlPageQuery.setQuerySql(querySql);
		return agingDao.findDataWithPage(sqlPageQuery);
	}
	
	
	@Override
	public PageResult<Object[]> listNodeAgingInfo(NodeAgingForm entity){
		
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append("select s.task_id, s.task_code,s.process_name,s.node_name,s.auditor_name, s.auditor_start_date,s.auditor_date,FORMAT(case when s.m<0 then 0 else s.m end ,2)  from   (select m.task_id,m.task_code,m.process_name,w.node_name,a.auditor_name, a.auditor_start_date,case when a.auditor_status ='0'  then null  else a.auditor_date end auditor_date,  (UNIX_TIMESTAMP(case when a.auditor_status ='0' or ifnull(a.auditor_date,0)=0  then now()  else a.auditor_date end)-ifnull(UNIX_TIMESTAMP(a.auditor_start_date),0))/3600 m  ,m.process_type from   wf_task_node_auditors a inner join wf_process_task_node w on a.task_node_id=w.task_node_id inner join wf_process_task_main m on w.task_id=m.task_id  WHERE   /**DATA_PERMISSION(m.org_id)**/ w.node_status in (3,5) and a.auditor_status in (2,4) and m.process_status in (0,3) and ((w.node_status = 3 or w.node_status = 5)  and a.auditor_status <> 0) ) s where 1=1");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (StringUtil.hasText(entity.getNodeStatus())) {
			sql.append(" and s.node_status_name = ? ");
			pageQuery.addParam(entity.getNodeStatus());
		}
		if (StringUtil.hasText(entity.getNodesNa())) {
			sql.append(" and s.node_name like ? ");
			pageQuery.addParam("%" + entity.getNodesNa() + "%");
		}
		if (StringUtil.hasText(entity.getNodeAuditors())) {
			sql.append(" and s.auditor_name like ?");
			pageQuery.addParam("%" + entity.getNodeAuditors() + "%");
		}
//		if (!StringUtil.isEmpty(entity.getDateStart())) {
//			sql.append(" AND  s.auditor_start_date >= ?");
//			pageQuery.addParam(entity.getDateStart());
//		}
//		if (!StringUtil.isEmpty(entity.getDateEnd())) {
//			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
//			pageQuery.addParam(entity.getDateEnd());
//		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_start_date >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		
		if (!StringUtil.isEmpty(entity.getNodeTimeS())) {
			sql.append(" AND  s.m >= ?");
			pageQuery.addParam( Float.valueOf(entity.getNodeTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getNodeTimeE())) {
			sql.append(" AND s.m < ?");
			pageQuery.addParam(Float.valueOf(entity.getNodeTimeE()));
		}
		sql.append(" order by s.auditor_date desc ");
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	
	
	@Override
	public PageResult<Object[]> listNodeAgingInfo2(NodeAgingForm entity){
		
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append("select s.task_id, s.task_code,s.process_name,s.node_name,s.auditor_name, s.auditor_start_date,s.auditor_date,FORMAT(case when s.m<0 then 0 else s.m end ,2)  from   (select m.task_id,m.task_code,m.process_name,w.node_name,a.auditor_name, a.auditor_start_date,case when a.auditor_status ='0'  then null  else a.auditor_date end auditor_date,  (UNIX_TIMESTAMP(case when a.auditor_status ='0' or ifnull(a.auditor_date,0)=0  then now()  else a.auditor_date end)-ifnull(UNIX_TIMESTAMP(a.auditor_start_date),0))/3600 m,m.process_type from   wf_task_node_auditors a inner join wf_process_task_node w on a.task_node_id=w.task_node_id inner join wf_process_task_main m on w.task_id=m.task_id  WHERE   /**DATA_PERMISSION(m.org_id)**/  w.node_status = 1 and m.process_status <> 9 and  a.auditor_status = 0 ) s where 1=1");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (StringUtil.hasText(entity.getNodeStatus())) {
			sql.append(" and s.node_status_name = ? ");
			pageQuery.addParam(entity.getNodeStatus());
		}
		if (StringUtil.hasText(entity.getNodesNa())) {
			sql.append(" and s.node_name like ? ");
			pageQuery.addParam("%" + entity.getNodesNa() + "%");
		}
		if (StringUtil.hasText(entity.getNodeAuditors())) {
			sql.append(" and s.auditor_name like ?");
			pageQuery.addParam("%" + entity.getNodeAuditors() + "%");
		}
//		if (!StringUtil.isEmpty(entity.getDateStart())) {
//			sql.append(" AND  s.auditor_start_date >= ?");
//			pageQuery.addParam(entity.getDateStart());
//		}
//		if (!StringUtil.isEmpty(entity.getDateEnd())) {
//			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
//			pageQuery.addParam(entity.getDateEnd());
//		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_start_date >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND s.auditor_start_date < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		
		if (!StringUtil.isEmpty(entity.getNodeTimeS())) {
			sql.append(" AND  s.m >= ?");
			pageQuery.addParam( Float.valueOf(entity.getNodeTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getNodeTimeE())) {
			sql.append(" AND s.m < ?");
			pageQuery.addParam(Float.valueOf(entity.getNodeTimeE()));
		}
		sql.append(" order by s.auditor_date desc ");
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	
	
	
	@Override
	public PageResult<Object[]> listProcessAgingInfo(ProcessAgingForm entity){
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name, date_format(s.auditor_stime,'%Y-%m-%d %H:%i:%s') auditor_stime,date_format(s.auditor_etime,'%Y-%m-%d %H:%i:%s'),  FORMAT(s.a,2) ");
		}else{
			sql.append("select s.task_id, s.task_code,s.province_name,s.process_name, s.auditor_stime,s.auditor_etime,  FORMAT(s.a,2)  ");
		}
		sql.append(" from (select r.province_name,r.province_code, m.task_id, m.process_type,m.task_code, m.process_name ,m.auditor_stime,m.auditor_etime, (UNIX_TIMESTAMP(IFNULL(m.auditor_etime,now()))-ifnull(UNIX_TIMESTAMP(m.auditor_stime),0))/3600 a ");
		sql.append(" from   wf_process_task_main m LEFT JOIN o2o_platform_regional r on m.org_id = r.platform_id where /**DATA_PERMISSION(m.org_id)**/ m.process_status in (0,9) ) s where 1=1 ");
		
		//sql.append("select s.task_id, s.task_code,s.process_name, s.auditor_stime,s.auditor_etime,  FORMAT(s.a,2),  s.process_status_name  from (select m.task_id, m.process_type,m.task_code, m.process_name ,m.auditor_stime,m.auditor_etime, (UNIX_TIMESTAMP(IFNULL(m.auditor_etime,now()))-ifnull(UNIX_TIMESTAMP(m.auditor_stime),0))/3600 a ,case when m.auditor_etime is null then '未结束'  else '已结束' end as process_status_name from   wf_process_task_main m where   /**DATA_PERMISSION(m.org_id)**/ m.process_status in (0,2,3,9) ");
		//sql.append("  ) s where 1=1 ");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getDateStart())) {
			sql.append(" AND  s.auditor_stime >= ?");
			pageQuery.addParam(entity.getDateStart());
		}
		if (!StringUtil.isEmpty(entity.getDateEnd())) {
			sql.append(" AND  s.auditor_stime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEnd());
		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_etime >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND  s.auditor_etime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeS())) {
			sql.append(" AND  s.a >= ?");
			pageQuery.addParam( Float.valueOf(entity.getProcessTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeE())) {
			sql.append(" AND s.a < ?");
			pageQuery.addParam(Float.valueOf(entity.getProcessTimeE()));
		}
		if (!StringUtil.isEmpty(entity.getProvince())) {
			sql.append(" AND s.province_code = ?");
			pageQuery.addParam(entity.getProvince());
		}
		sql.append(" order by s.auditor_etime desc ");
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	@Override
	public PageResult<Object[]> listProcessAgingInfo2(ProcessAgingForm entity){
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, entity.getPageSize(),entity.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if(StringUtils.isNotBlank(entity.getOper()) && StringUtils.equalsIgnoreCase(entity.getOper(), "download")){
			sql.append("select s.task_code,s.province_name,s.process_name, date_format(s.auditor_stime,'%Y-%m-%d %H:%i:%s') auditor_stime,date_format(s.auditor_etime,'%Y-%m-%d %H:%i:%s'),  FORMAT(s.a,2) ");
		}else{
			sql.append("select s.task_id, s.task_code,s.province_name,s.process_name, s.auditor_stime,s.auditor_etime,  FORMAT(s.a,2)  ");
		}
		sql.append(" from (select r.province_name,r.province_code, m.task_id, m.process_type,m.task_code, m.process_name ,m.auditor_stime,m.auditor_etime, (UNIX_TIMESTAMP(IFNULL(m.auditor_etime,now()))-ifnull(UNIX_TIMESTAMP(m.auditor_stime),0))/3600 a ");
		sql.append(" from   wf_process_task_main m LEFT JOIN o2o_platform_regional r on m.org_id = r.platform_id where /**DATA_PERMISSION(m.org_id)**/ m.process_status in (2,3) ) s where 1=1 ");
		
		//sql.append("select s.task_id, s.task_code,s.process_name, s.auditor_stime,s.auditor_etime,  FORMAT(s.a,2),  s.process_status_name  from (select m.task_id, m.process_type,m.task_code, m.process_name ,m.auditor_stime,m.auditor_etime, (UNIX_TIMESTAMP(IFNULL(m.auditor_etime,now()))-ifnull(UNIX_TIMESTAMP(m.auditor_stime),0))/3600 a ,case when m.auditor_etime is null then '未结束'  else '已结束' end as process_status_name from   wf_process_task_main m where   /**DATA_PERMISSION(m.org_id)**/ m.process_status in (0,2,3,9) ");
		//sql.append("  ) s where 1=1 ");
		if (!CollectionUtils.isEmpty(entity.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and s.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < entity.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(entity.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(entity.getBillCode())) {
			sql.append(" and s.task_code = ? ");
			pageQuery.addParam(entity.getBillCode());
		}
		if (!StringUtil.isEmpty(entity.getDateStart())) {
			sql.append(" AND  s.auditor_stime >= ?");
			pageQuery.addParam(entity.getDateStart());
		}
		if (!StringUtil.isEmpty(entity.getDateEnd())) {
			sql.append(" AND  s.auditor_stime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEnd());
		}
		if (!StringUtil.isEmpty(entity.getDateStartb())) {
			sql.append(" AND  s.auditor_etime >= ?");
			pageQuery.addParam(entity.getDateStartb());
		}
		if (!StringUtil.isEmpty(entity.getDateEndb())) {
			sql.append(" AND  s.auditor_etime < ADDDATE(?,INTERVAL 1 day)");
			pageQuery.addParam(entity.getDateEndb());
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeS())) {
			sql.append(" AND  s.a >= ?");
			pageQuery.addParam( Float.valueOf(entity.getProcessTimeS()));
		}
		if (!StringUtil.isEmpty(entity.getProcessTimeE())) {
			sql.append(" AND s.a < ?");
			pageQuery.addParam(Float.valueOf(entity.getProcessTimeE()));
		}
		if (!StringUtil.isEmpty(entity.getProvince())) {
			sql.append(" AND s.province_code = ?");
			pageQuery.addParam(entity.getProvince());
		}
		sql.append(" order by s.auditor_etime desc ");
		pageQuery.setQuerySql(sql.toString());
		return agingDao.findDataWithPage(pageQuery);
	}
	
	@Override
	public List<Object[]> getProcessType(String referId) {
		return agingDao.findObjectsList("select process_type from wf_process_task_main  where refer_id = ? ", referId);
	}
	
}
